# coding=utf-8

__author__ = 'tzq'
import pymysql


class DBHelper:

    def __init__(self):
        self.dbname = 'SpiderData'
        self.host = '39.107.29.173'
        self.user = 'root'
        self.passwd = 'Root!2019'
        self.port = 3306

        # self.dbname = 'SpiderDB'
        # self.host = '55936bea3d360.gz.cdb.myqcloud.com'
        # self.user = 'root'
        # self.passwd = 'Tan520521'
        # self.port = 3754

    def QueryData(self, quertysql, count):
        try:
            conn = pymysql.connect(host=self.host, user=self.user, passwd=self.passwd,
                                   db=self.dbname, port=self.port, charset="utf8")
            cur = conn.cursor()
            cur.execute(quertysql)
            results = cur.fetchmany(count)
            cur.close()
            conn.close()
            return results
        except pymysql.Error as e:
            print("Mysql Error %d: %s" % (e.args[0], e.args[1]))

    def InsertData(self, JokerName, JokeContent, Lauds):
        conn = pymysql.connect(host=self.host, user=self.user, passwd=self.passwd, db=self.dbname, port=self.port,
                               charset="utf8")
        try:
            cur = conn.cursor()
            cur.execute("insert into Qiushibaike(JokerName ,JokeContent, Lauds, IsDelete) value (%s,%s,%s,0);",
                        (str(JokerName), str(JokeContent), int(Lauds)))
            print
            "Number of rows insert: %d" % cur.rowcount
            cur.close()
            conn.commit()
        except pymysql.Error as e:
            print("Mysql Error %d: %s" % (e.args[0], e.args[1]))
        finally:
            conn.close()

    def InsertImageUrlInfo(self, imageTheme, imageUrl):

        conn = pymysql.connect(host=self.host, user=self.user, passwd=self.passwd, db=self.dbname, port=self.port,
                               charset="utf8")
        try:
            cur = conn.cursor()

            cur.execute("insert into beautygirls(imagetheme ,imageurl, download,deleted) value (%s,%s,0,0);",
                        (str(imageTheme), str(imageUrl)))
            print("Number of rows insert: %d" % cur.rowcount)
            cur.close()
            conn.commit()
        except pymysql.Error as e:
            print("Mysql Error %d: %s" % (e.args[0], e.args[1]))
        finally:
            conn.close()

    def InserHouseDatainfo(self, resblock_name, resblock_type, sale_status, resblock_location, resblock_area,
                           resblock_tag, peice, peice_unit, source_type):
        conn = pymysql.connect(host=self.host, user=self.user, passwd=self.passwd, db=self.dbname, port=self.port,
                               charset="utf8")
        try:
            # SQL 插入语句
            sql = "insert into HouseSource(resblock_name,resblock_type,sale_status,resblock_location,resblock_area,resblock_tag,peice,peice_unit,source_type,create_time) \
                  value('%s','%s','%s','%s','%s','%s',%s,'%s','%s',now());" % \
                  (str(resblock_name), str(resblock_type), str(sale_status), str(resblock_location), str(resblock_area),
                   str(resblock_tag), float(peice), str(peice_unit), str(source_type))

            cur = conn.cursor()
            cur.execute(sql)
            cur.close()
            conn.commit()
        except pymysql.Error as e:
            print("Mysql Error %d: %s" % (e.args[0], e.args[1]))
        finally:
            conn.close()

    def GetImageUrlInfo(self, imageTheme, imageUrl):

        conn = pymysql.connect(host=self.host, user=self.user, passwd=self.passwd, db=self.dbname, port=self.port,
                               charset="utf8")
        try:
            cur = conn.cursor()
            cur.execute(
                "select imagetheme ,imageurl, download,deleted from  beautygirls where imagetheme=%s and imageurl = %s ;",
                (str(imageTheme), str(imageUrl)))
            results = cur.fetchmany(1)
            cur.close()
            return results
        except pymysql.Error as e:
            print("Mysql Error %d: %s" % (e.args[0], e.args[1]))
        finally:
            conn.close()
